﻿Imports System.Data.OleDb
Imports System.Net.Mail
Public Class Meerdereaandeelhouders
    Inherits System.Web.UI.Page
    Dim strgekozenVergadering As String
    Dim strcode() As String

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If IsPostBack = False Then
            Dim cnndb3 As New OleDbConnection
            Dim cmdCommando As New OleDbCommand
            Dim dtrDataReader As OleDbDataReader
            Dim strsqlEmailadress As String
            Dim strsqlVergaderingen
            strsqlEmailadress = "SELECT [E-mail] FROM [tblAandeelhouders]"
            cnndb3.ConnectionString = My.Settings.csAandeelhouders
            cmdCommando.Connection = cnndb3
            cmdCommando.CommandText = strsqlEmailadress


            cnndb3.Open()
            dtrDataReader = cmdCommando.ExecuteReader
            Do While dtrDataReader.Read()
                lstAlleaandeelhouders.Items.Add(dtrDataReader.Item("E-mail"))
                cblEmail.Items.Add(dtrDataReader.Item("E-mail"))

            Loop
            cnndb3.Close()


            strsqlVergaderingen = "SELECT [ID], [Titel] FROM [tblVergaderingen]"
            cmdCommando.CommandText = strsqlVergaderingen
            cnndb3.Open()
            dtrDataReader = cmdCommando.ExecuteReader
            Do While dtrDataReader.Read()
                dpdVergadering.Items.Add(dtrDataReader.Item("Titel"))
            Loop
            cnndb3.Close()
        End If

    End Sub

    Protected Sub btnalleslinkerkant_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnalleslinkerkant.Click
        lstAandeelhoudersmailversturennaar.Items.Clear()
        Dim cnndb3 As New OleDbConnection
        Dim cmdCommando As New OleDbCommand
        Dim dtrDataReader As OleDbDataReader
        Dim strsqlEmailadress As String
        strsqlEmailadress = "SELECT [E-mail] FROM [tblAandeelhouders]"
        cnndb3.ConnectionString = My.Settings.csAandeelhouders
        cmdCommando.Connection = cnndb3
        cmdCommando.CommandText = strsqlEmailadress


        cnndb3.Open()
        dtrDataReader = cmdCommando.ExecuteReader
        Do While dtrDataReader.Read()
            lstAlleaandeelhouders.Items.Add(dtrDataReader.Item("E-mail"))
        Loop
        cnndb3.Close()
    End Sub

    Protected Sub btnAlleadressenrechterkant_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAlleadressenrechterkant.Click
        lstAlleaandeelhouders.Items.Clear()

        Dim cnndb3 As New OleDbConnection
        Dim cmdCommando As New OleDbCommand
        Dim dtrDataReader As OleDbDataReader
        Dim strsqlEmailadress As String
        Dim intTeller As Integer = -1
        strsqlEmailadress = "SELECT [E-mail],[Code] FROM [tblAandeelhouders]"
        cnndb3.ConnectionString = My.Settings.csAandeelhouders
        cmdCommando.Connection = cnndb3
        cmdCommando.CommandText = strsqlEmailadress


        cnndb3.Open()
        dtrDataReader = cmdCommando.ExecuteReader
        Do While dtrDataReader.Read()
            intTeller += 1
            ReDim Preserve strcode(intTeller)
            lstAandeelhoudersmailversturennaar.Items.Add(dtrDataReader.Item("E-mail"))
            strcode(intTeller) = dtrDataReader.Item("Code")
        Loop
        cnndb3.Close()
    End Sub

    Protected Sub btngeselecteerderechterkant_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btngeselecteerderechterkant.Click
        Dim stremailadres As String
        stremailadres = lstAlleaandeelhouders.SelectedItem.Text
        If stremailadres IsNot "" Then
            lstAandeelhoudersmailversturennaar.Items.Add(stremailadres)
            lstAlleaandeelhouders.Items.Remove(lstAlleaandeelhouders.SelectedItem)
        End If
    End Sub

    Protected Sub btnGeselecteerdelinkerkant_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnGeselecteerdelinkerkant.Click
        Dim stremailadres As String
        stremailadres = lstAandeelhoudersmailversturennaar.SelectedItem.Text
        If stremailadres IsNot "" Then
            lstAlleaandeelhouders.Items.Add(stremailadres)
            lstAandeelhoudersmailversturennaar.Items.Remove(lstAandeelhoudersmailversturennaar.SelectedItem)
        End If
    End Sub


    Public Function myReplace() As Boolean
        Static strDatum1 As String = "[$date1$]"
        Static strDatum2 As String = "[$date2$]"
        Static strPlaats As String = "[$plaats$]"
        Dim strDatum1Db As String
        Dim strDatum2Db As String
        Dim strPlaatsDb As String
        Dim cnndb3 As New OleDbConnection
        Dim cmdCommando As New OleDbCommand
        Dim dtrDataReader2 As OleDbDataReader
        Dim strsqlEmailadress As String


        strsqlEmailadress = "SELECT [Start], [Eind], [Plaats] FROM [tblVergaderingen] WHERE [ID] ='" & strgekozenVergadering & "'"
        cnndb3.ConnectionString = My.Settings.csAandeelhouders
        cmdCommando.Connection = cnndb3
        cmdCommando.CommandText = strsqlEmailadress


        cnndb3.Open()
        dtrDataReader2 = cmdCommando.ExecuteReader
        Do While dtrDataReader2.Read()
            strDatum1Db = dtrDataReader2.Item("Start")
            strDatum2Db = dtrDataReader2.Item("Eind")
            strPlaatsDb = dtrDataReader2.Item("Plaats")

        Loop
        cnndb3.Close()
        If IsNothing(dtrDataReader2) Then
            dtrDataReader2.Close()
        End If
        mailContent.InnerHtml = mailContent.InnerHtml.Replace(strDatum1, strDatum1Db)
        mailContent.InnerHtml = mailContent.InnerHtml.Replace(strDatum2, strDatum2Db)
        mailContent.InnerHtml = mailContent.InnerHtml.Replace(strPlaats, strPlaatsDb)
        mailContent.InnerHtml = mailContent.InnerHtml.Replace(".uniqu.", "<a href=AanofAfwezig.aspx?id=" & strcode(0) & ">Klik hier voor aanwezig of afwezig</a>")


        strDatum1 = strDatum1Db
        strDatum2 = strDatum2Db
        strPlaats = strPlaatsDb


        btnVersturen.Enabled = True
        Return True

    End Function

    Protected Sub btnMenu_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnMenu.Click
        Server.Transfer("default.aspx")
    End Sub

    Protected Sub btnVoorbeeld_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnVoorbeeld.Click
        strgekozenVergadering = dpdVergadering.Text

        myReplace()
    End Sub

    Protected Sub btnVersturen_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnVersturen.Click
        'declareer variabele
        myReplace()
        Dim objMail As New MailMessage()
        Dim objMijnAdres As New MailAddress("Proventi@hotmail.be", "Proventi")
        Dim objSmtp As New SmtpClient(My.Settings.SMTPServer)
        Dim strAan As String
        Dim intAantalAandeelhouders As Integer
        Dim strEmailadress() As String
        Dim strHyperlinkOude, strHyperlinkNieuwe As String
        intAantalAandeelhouders = lstAandeelhoudersmailversturennaar.Items.Count
        ReDim Preserve strEmailadress(intAantalAandeelhouders - 1)
        For intTeller As Integer = 0 To intAantalAandeelhouders - 1
            strEmailadress(intTeller) = lstAandeelhoudersmailversturennaar.Items(intTeller).Text
        Next
        For intTeller As Integer = 0 To intAantalAandeelhouders - 1
            strAan = strEmailadress(intTeller)
            'stel de verzender en ontvanger in 
            objMail.From = objMijnAdres
            objMail.To.Add(strAan)
            'stel het subject en de tekst in 
            objMail.Subject = txtOnderwerp.Text
            If intTeller = 0 Then
                'nothing
            Else
                strHyperlinkOude = "<a href=AanofAfwezig.aspx?id=" & strcode(intTeller) & ">Klik hier voor aanwezig of afwezig</a>"
                strHyperlinkNieuwe = "<a href=AanofAfwezig.aspx?id=" & strcode(intTeller + 1) & ">Klik hier voor aanwezig of afwezig</a>"
            End If
            mailContent.InnerHtml = mailContent.InnerHtml.Replace(strHyperlinkOude, strHyperlinkNieuwe)
            objMail.Body = mailContent.InnerText
            objMail.IsBodyHtml = True
            'verzend het bericht 
            objSmtp.Send(objMail)

        Next
        lblMailVerstuurd.Text = "De mailtjes zijn verstuurd"

    End Sub
End Class